﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq.Mapping;
using System.Data.Linq;

namespace LinqToSQL
{
    // Add a method to the generated Customer class to
    // show formatted customer properties.
    public partial class Customer
    {
        public override string ToString()
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("{0} {1} {2}",
            FirstName, LastName, EmailAddress);
            foreach (CustomerAddress ca in CustomerAddresses)
            {
                sb.AppendFormat("\n\t{0}, {1}",
                ca.Address.AddressLine1,
                ca.Address.City);
            }
            sb.AppendLine();
            return sb.ToString();
        }
    }
}

namespace LinqToSQL.stuff
{
    static public class LinqToSQLExamples
    {
        static public class FirstExample
        {
            // customer class
            [Table(Name = "SalesLT.Customer")]
            public class Customer
            {
                [Column]
                public string FirstName { get; set; }
                [Column]
                public string LastName { get; set; }
                [Column]
                public string EmailAddress { get; set; }
                // Overrides the Object.ToString( ) to provide a
                // string representation of the object properties.
                public override string ToString()
                {
                    return string.Format("{0} {1}\nEmail: {2}",
                    FirstName, LastName, EmailAddress);
                }
            }

            public static void run()
            {
                DataContext db = new DataContext(@"Data Source=localhost;Initial Catalog=AdventureWorksLT;Integrated Security=True");
                Table<Customer> customers = db.GetTable<Customer>();
                var query =
                    from customer in customers
                    where customer.FirstName == "Donna"
                    select customer;
                foreach (var c in query)
                    Console.WriteLine(c.ToString());
            }
        }

        static public class SecondExample
        {
            static public void run()
            {
                AdventureWorksAddressDataContext dc = new AdventureWorksAddressDataContext();
                // Uncomment the statement below to show the
                // SQL statement generated by LINQ to SQL.
                // dc.Log = Console.Out;
                // Find one customer record.
                Customer donna = dc.Customers.Single(c => c.CustomerID == 1);
                Console.WriteLine(donna);
                // Find a list of customer records.
                var customerDs =
                from customer in dc.Customers
                where customer.FirstName.StartsWith("D")
                orderby customer.FirstName, customer.LastName
                select customer;
                foreach (Customer customer in customerDs)
                {
                    Console.WriteLine(customer);
                }
            }
        }

        static public class ModifyDataExample
        {
            public static void run()
            {
                AddCustomer();
                UpdateCustomer();
                //Console.ReadKey();
            }

            private static void AddCustomer()
            {
                Console.WriteLine("Adding a new customer...");
                AdventureWorksAddressDataContext dc = new AdventureWorksAddressDataContext();
                // Uncomment the statement below to show the
                // SQL statement generated by LINQ to SQL.
                // dc.Log = Console.Out;
                // Add a new customer with address
                Customer douglas = new Customer();
                douglas.FirstName = "Douglas";
                douglas.LastName = "Adams";
                douglas.EmailAddress = "douglas0@adventureworks.com";
                douglas.PasswordHash = "fake";
                douglas.PasswordSalt = "fake";
                douglas.ModifiedDate = DateTime.Today;
                douglas.rowguid = Guid.NewGuid();
                Address addr = new Address();
                addr.AddressLine1 = "1c Sharp Way";
                addr.City = "Seattle";
                addr.PostalCode = "98011";
                addr.StateProvince = "Washington";
                addr.CountryRegion = "United States";
                addr.ModifiedDate = DateTime.Today;
                addr.rowguid = Guid.NewGuid();
                CustomerAddress ca = new CustomerAddress();
                ca.AddressType = "Main Office";
                ca.Address = addr;
                ca.Customer = douglas;
                ca.ModifiedDate = DateTime.Today;
                ca.rowguid = Guid.NewGuid();
                dc.Customers.InsertOnSubmit(douglas);
                dc.SubmitChanges();
                ShowCustomersByFirstName("Douglas");
            }
            // Update a customer record
            private static void UpdateCustomer()
            {
                Console.WriteLine("Updating a customer...");
                AdventureWorksAddressDataContext dc = new AdventureWorksAddressDataContext();
                // Uncomment the statement below to show the
                // SQL statement generated by LINQ to SQL.
                //dc.Log = Console.Out;
                Customer dAdams = dc.Customers.First(c => (c.FirstName == "Douglas" && c.LastName == "Adams"));
                Console.WriteLine("Before:\n{0}", dAdams);
                dAdams.Title = "Mr.";
                // Add a new shipping address
                Address addr = new Address();
                addr.AddressLine1 = "1 Warehouse Place";
                addr.City = "Los Angeles";
                addr.PostalCode = "30210";
                addr.StateProvince = "California";
                addr.CountryRegion = "United States";
                addr.ModifiedDate = DateTime.Today;
                addr.rowguid = Guid.NewGuid();
                CustomerAddress ca = new CustomerAddress();
                ca.AddressType = "Shipping";
                ca.Address = addr;
                ca.Customer = dAdams;
                ca.ModifiedDate = DateTime.Today;
                ca.rowguid = Guid.NewGuid();
                dc.SubmitChanges();
                Customer dAdams1 = dc.Customers.First(c => (c.FirstName == "Douglas" && c.LastName == "Adams"));
                Console.WriteLine("After:\n{0}", dAdams);
            }
            // Find a list of customer records with a specific first name.
            private static void ShowCustomersByFirstName(string firstName)
            {
                AdventureWorksAddressDataContext dc = new AdventureWorksAddressDataContext();
                var customers =
                from customer in dc.Customers
                where customer.FirstName == "Douglas"
                orderby customer.FirstName, customer.LastName
                select customer;
                Console.WriteLine("Customers whose first name is {0}:", firstName);
                foreach (Customer customer in customers)
                    Console.WriteLine(customer);
            }

        }
    }
}
